package com.lx.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileOutputStream;

public class WriteExcel {
    String PATH = "E:\\ok\\poi\\";
    @Test
    public void testPoi03() throws Exception {
        // 1、创建一个工作簿
        Workbook workbook =  new HSSFWorkbook();
        // 2、创建一个工作表
        Sheet sheet = workbook.createSheet("使用poi创建03版sheet页");
        // 3、创建一行
        // Row row = sheet.createRow(0);
        // // 4、创建一个单元格
        // Cell cell = row.createCell(0);
        // // 5、给单元格赋值
        // cell.setCellValue("观看人数");

        //获取第一行
        Row row0 = sheet.createRow(0);
        //第一行第一列赋值
        row0.createCell(0).setCellValue("观看人数");
        //第一行第二列赋值
        row0.createCell(1).setCellValue("观看时间");

        //获取第二行
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1000");
        row1.createCell(1).setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "poi03.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("使用poi创建03xls文件，创建成功！");
    }
    @Test
    public void testPoi07() throws Exception {
        // 1、创建一个工作簿
        Workbook workbook =  new XSSFWorkbook();
        // 2、创建一个工作表
        Sheet sheet = workbook.createSheet("使用poi创建07版sheet页");
        // 3、创建一行
        // Row row = sheet.createRow(0);
        // // 4、创建一个单元格
        // Cell cell = row.createCell(0);
        // // 5、给单元格赋值
        // cell.setCellValue("观看人数");

        //获取第一行
        Row row0 = sheet.createRow(0);
        //第一行第一列赋值
        row0.createCell(0).setCellValue("观看人数");
        //第一行第二列赋值
        row0.createCell(1).setCellValue("观看时间");

        //获取第二行
        Row row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1000");
        row1.createCell(1).setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "poi07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("使用poi创建07xlsx文件，创建成功！");
    }

    @Test
    public void testPoi03BigData() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook =  new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("使用poi创建03版sheet页");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 9; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "poi03BigData.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("使用poi创建大数据03xls文件，创建成功！");
        System.out.println("耗时：" + (System.currentTimeMillis() - start)/1000 + "s");
    }

    @Test
    public void testPoi07BigData() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook =  new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("使用poi创建07版sheet页");
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 9; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "poi07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("使用poi创建大数据07xlsx文件，创建成功！");
        System.out.println("耗时：" + (System.currentTimeMillis() - start)/1000 + "s");
    }

    @Test
    public void testPoi07BigDataSuper() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook =  new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("使用poi创建07版sheet页");
        for (int i = 0; i < 200000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 9; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "poi07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清理临时文件
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("使用poi创建大数据07xlsx文件，创建成功！");
        System.out.println("耗时：" + (System.currentTimeMillis() - start)/1000 + "s");
    }
}
